﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Convert.DataAccess;
using Convert.LOTSmodel;
using Convert.CPIAMmodel;
using System.Configuration;
using System.Data.OleDb;
using System.Windows;
namespace Convert.DBconverter
{
    class PersonsLaybysConverter
    {
        private static int BATCH_SAVE_SIZE = 5000;
        public static string LotsConnectionString; //= @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\LOTS\lots.mdb";//@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\LOTS\lots.mdb";//ConfigurationManager.ConnectionStrings["LOTStoCPIAMconvert.Properties.Settings.lotsConnectionString"].ConnectionString;
        public static string CpiamConnectionString; //= ConfigurationManager.ConnectionStrings["LOTStoCPIAMconvert.Properties.Settings.CPIAMPOSMaxRetailConnectionString"].ConnectionString;

        public static void ConvertPersonsLaybys(string lotsConnectionString, string cpiamConnectionString)
        {
            LotsConnectionString = lotsConnectionString;
            CpiamConnectionString = cpiamConnectionString;
            DataTable ClientDT = DataTables.CreateClientFileDT();
            DataTable ClubMembersDT = DataTables.CreateClubMembersDT();
            DataTable PatientsDT = DataTables.CreatePatientsFileDT();
            DataTable LaybyContractsDT = DataTables.CreateLaybyContractDT();
            OleDbConnection dbConn = new OleDbConnection(LotsConnectionString);
            try
            {
                dbConn.Open();
                using (OleDbCommand command = dbConn.CreateCommand())
                {
                    command.CommandText = string.Format(@"SELECT Person.PersonID, Person.FirstName, Person.LastName, Title.Title, Person.Address, Person.Suburb, Person.PostCode, Person.PhoneNumber, Person.BirthDate, Person.MedicareNo, Person.AccountID, Layby.LaybyID, Layby.CreatedDate, Layby.FinishDate, Layby.Status
                    FROM ((Person INNER JOIN Layby ON Person.PersonID = Layby.PersonID) LEFT OUTER JOIN Title ON Person.TitleID = Title.TitleID)
                    WHERE (Person.PersonID > 0)");
                    OleDbDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                            PersonLayby personLayby = new PersonLayby();
                            int i = 0;
                            personLayby.PersonID = reader.IsDBNull(i) ? 0 : reader.GetInt32(i);
                            personLayby.FirstName = reader.IsDBNull(++i) ? null : reader.GetString(i);
                            personLayby.LastName = reader.IsDBNull(++i) ? null : reader.GetString(i);
                            personLayby.Title = reader.IsDBNull(++i) ? null : reader.GetString(i);
                            personLayby.Address = reader.IsDBNull(++i) ? null : reader.GetString(i);
                            personLayby.Suburb = reader.IsDBNull(++i) ? null : reader.GetString(i);
                            personLayby.PostCode = reader.IsDBNull(++i) ? null : reader.GetString(i);
                            personLayby.PhoneNumber = reader.IsDBNull(++i) ? null : reader.GetString(i);
                            personLayby.BirthDate = reader.IsDBNull(++i) ? DateTime.Now:reader.GetDateTime(i);
                            personLayby.MedicareNo = reader.IsDBNull(++i) ? null : reader.GetString(i);
                            personLayby.AccountID = reader.IsDBNull(++i) ? 0 : reader.GetInt32(i);
                            personLayby.LaybyID = reader.IsDBNull(++i) ? 0 : reader.GetInt32(i);
                            personLayby.CreatedDate = reader.IsDBNull(++i) ? DateTime.Now : reader.GetDateTime(i);
                            personLayby.FinishDate = reader.IsDBNull(++i) ? DateTime.Now : reader.GetDateTime(i);
                            personLayby.Status = reader.IsDBNull(++i) ? 0 : reader.GetInt32(i);
                            SavePersonLayby(personLayby, ClientDT, ClubMembersDT, PatientsDT, LaybyContractsDT);

                        if (ClientDT.Rows.Count % BATCH_SAVE_SIZE == 0)
                        {
                            SaveAllDataTables(CpiamConnectionString, ClientDT, ClubMembersDT, PatientsDT, LaybyContractsDT);
                        }
                    }
                    SaveAllDataTables(CpiamConnectionString, ClientDT, ClubMembersDT, PatientsDT, LaybyContractsDT);
                }
                UpdateLayby();
            }
            catch (Exception ex)
            {
                Logger.LogWarningAndError("Error in Convert PersonLayby ", ex);
                MessageBox.Show("Error converting PersonLayby: " + ex.Message + "\n\nStackTrace=" + ex.StackTrace);
            }
            finally
            {
                dbConn.Close();
            }

        }

        private static void SaveAllDataTables(String ConnectionString, DataTable ClientDT, DataTable ClubMembersDT, DataTable PatientsDT, DataTable LaybyContractsDT)
        {
            if (ClientDT.Rows.Count > 0)
                dbSaveBatch.SaveBatch(ClientDT, "ClientsFile", ConnectionString);
            if (ClubMembersDT.Rows.Count > 0)
                dbSaveBatch.SaveBatch(ClubMembersDT, "ClubMembersFile", ConnectionString);
            if (PatientsDT.Rows.Count > 0)
                dbSaveBatch.SaveBatch(PatientsDT, "PatientsFile", ConnectionString);
            if (LaybyContractsDT.Rows.Count > 0)
                dbSaveBatch.SaveBatch(LaybyContractsDT, "LaybyContracts", ConnectionString);
        }

        private static void SavePersonLayby(PersonLayby personLayby, DataTable ClientDT, DataTable ClubMembersDT, DataTable PatientsDT, DataTable LaybyContractsDT)
        {
            Guid ClientID = Guid.Empty;
            int ClientNo = personLayby.PersonID;
            string ClientSex = "Unknown";
            string ClientPhoneNumber = string.Empty;
            string ClientSalu = string.Empty;
            DateTime ClientBirthDate = DateTime.Now;
            string ClientPostalPCode = "0000";
            string ClientFirstName = string.Empty;
            string ClientPostalState = "AAA";
            string ClientPostalAddress = "Address Not Supplied";
            string ClientPostalSuburb = "None";
            string ClientSearchMarker = string.Empty;
            string ClientIDType = "Not Identified";
            string ClientIDDetails = "A";
            ClientPhoneNumber = personLayby.PhoneNumber == null ? string.Empty : personLayby.PhoneNumber;
            if (personLayby.Title == null)
            {
                ClientSex = "U";
                ClientSalu = String.Empty;
            }
            else
            {
                ClientSalu = personLayby.Title;
                if (personLayby.Title.ToUpper() == "MR")
                    ClientSex = "M";
                else if (personLayby.Title.ToUpper() == "MR.")
                    ClientSex = "M";
                else if (personLayby.Title.ToUpper() == "FR")
                    ClientSex = "M";
                else if (personLayby.Title.ToUpper() == "MISS")
                    ClientSex = "F";
                else if (personLayby.Title.ToUpper() == "MRS")
                    ClientSex = "F";
                else if (personLayby.Title.ToUpper() == "MS")
                    ClientSex = "F";
                else if (personLayby.Title.ToUpper() == "MS.")
                    ClientSex = "F";
                else if (personLayby.Title.ToUpper() == "MASTER")
                    ClientSex = "M";
                else if (personLayby.Title.ToUpper() == "BR")
                    ClientSex = "M";
                else if (personLayby.Title.ToUpper() == "SIR")
                    ClientSex = "M";
                else if (personLayby.Title.ToUpper() == "LADY")
                    ClientSex = "F";
                else if (personLayby.Title.ToUpper() == "MSTR")
                    ClientSex = "M";
                else
                    ClientSex = "U";
            }

            ClientBirthDate = personLayby.BirthDate;
            ClientPostalPCode = personLayby.PostCode == null ? "0000" : personLayby.PostCode;
            ClientFirstName = personLayby.FirstName == null ? string.Empty : personLayby.FirstName.Replace("!", "").Replace("''", "").Replace("#", "").Replace("*", "").Replace("(", "").Replace(".", "").Replace("@", "").Replace("^", "").Replace("_", "").Trim().ToUpper();
            if (personLayby.PostCode.StartsWith("2"))
                ClientPostalState = "NSW";
            else if (personLayby.PostCode.StartsWith("3"))
                ClientPostalState = "QLD";
            else if (personLayby.PostCode.StartsWith("4"))
                ClientPostalState = "VIA";
            else
                ClientPostalState = "AAA";

            ClientPostalAddress = personLayby.Address == null ? "Address Not Supplied" : personLayby.Address;
            ClientPostalSuburb = personLayby.Suburb == null ? "None" : personLayby.Suburb;
            ClientSearchMarker = personLayby.LastName.Length >= 2 ? personLayby.LastName.Trim().Substring(0, 2).ToUpper() : personLayby.LastName.ToUpper();
            if (personLayby.MedicareNo == null)
            {
                ClientIDType = "Not Identified";
                ClientIDDetails = string.Empty;
            }
            else
            {
                if (personLayby.MedicareNo.Length > 0)
                {
                    ClientIDType = "Medicare Number";
                    ClientIDDetails = personLayby.MedicareNo;
                }
                else
                {
                    ClientIDType = "Not Identified";
                    ClientIDDetails = string.Empty;
                }
            }

            if (personLayby.PersonID != personLayby.AccountID || personLayby.AccountID == 0)
            {

                ClientID = CheckClientExists(personLayby.LastName.ToUpper(), personLayby.FirstName.ToUpper(), personLayby.Address.ToUpper(), personLayby.PostCode.ToUpper());
                if (ClientID == Guid.Empty)
                {
                    ClientID = Guid.NewGuid();
                    Client client = new Client(ClientID, ClientNo, "None", 1, DateTime.Now, "System",
                          personLayby.LastName.Trim().ToUpper(),
                          personLayby.FirstName.Trim(), ClientSearchMarker,
                          ClientSalu, ClientSex, ClientBirthDate, ClientPostalAddress,
                          ClientPostalSuburb, ClientPostalState, ClientPostalPCode,
                          ClientPostalAddress, ClientPostalSuburb, ClientPostalState, ClientPostalPCode,
                          ClientPhoneNumber, String.Empty, String.Empty,
                          String.Empty, true, DateTime.Now, "A", "General",
                          ClientIDType, ClientIDDetails);
                    Client.SaveClientToDataTable(client, ClientDT);
                    ClubMembers clubMember = new ClubMembers(ClientID, 0, 0, 0, 0, false, false);
                    ClubMembers.SaveClubMembersToDataTable(clubMember, ClubMembersDT);
                    Patients patient = new Patients(ClientID);
                    Patients.SavePatientsToDataTable(patient, PatientsDT);
                }
            }
            else
            {
                ClientID = GetClientIDFromOLDPOSDebtorID(personLayby.AccountID);
                if (ClientID == Guid.Empty)
                {
                    ClientID = Guid.NewGuid();
                    Client client = new Client(ClientID, ClientNo, "None", 1, DateTime.Now, "System",
                          personLayby.LastName.Trim().ToUpper(),
                          personLayby.FirstName.Trim(), ClientSearchMarker,
                          ClientSalu, ClientSex, ClientBirthDate, ClientPostalAddress,
                          ClientPostalSuburb, ClientPostalState, ClientPostalPCode,
                          ClientPostalAddress, ClientPostalSuburb, ClientPostalState, ClientPostalPCode,
                          ClientPhoneNumber, String.Empty, String.Empty,
                          String.Empty, true, DateTime.Now, "A", "General",
                          ClientIDType, ClientIDDetails);
                    Client.SaveClientToDataTable(client, ClientDT);
                    ClubMembers clubMember = new ClubMembers(ClientID, 0, 0, 0, 0, false, false);
                    ClubMembers.SaveClubMembersToDataTable(clubMember, ClubMembersDT);
                    Patients patient = new Patients(ClientID);
                    Patients.SavePatientsToDataTable(patient, PatientsDT);
                }
            }
            string strLaybyStatus = string.Empty;
            if (personLayby.Status != 1 || personLayby.Status != 2 || personLayby.Status != 3)
                strLaybyStatus = "A";
            Guid LaybyHeader = Guid.NewGuid();
            LaybyContracts laybyContracts = new LaybyContracts(LaybyHeader, ClientID, strLaybyStatus, personLayby.CreatedDate, personLayby.FinishDate, personLayby.LaybyID);
            LaybyContracts.SaveLaybyContractsToDataTable(laybyContracts, LaybyContractsDT);
            ConvertLaybyTrans(personLayby.PersonID, LaybyHeader);
        }


        public static void ConvertLaybyTrans(int PersonID, Guid LaybyHeaderID)
        {
            DataTable LaybyTransDT = DataTables.CreateLaybyTransDT();
            OleDbConnection dbConn = new OleDbConnection(LotsConnectionString);
            try
            {
                dbConn.Open();
                using (OleDbCommand command = dbConn.CreateCommand())
                {
                    command.CommandText = string.Format(@"SELECT Layby.LaybyID, Sales.SaleID, Sales.TimeDate, SaleItem.StockID, SaleItem.Description, SaleItem.Cost, SaleItem.Retail, SaleItem.Quantity, SaleItem.GSTPaid
                    FROM ((Layby INNER JOIN Sales ON Layby.LaybyID = Sales.LaybyID) INNER JOIN SaleItem ON Sales.SaleID = SaleItem.SaleID)
                    WHERE (Layby.LaybyID = @ID) ORDER BY Sales.TimeDate");
                    command.Parameters.AddWithValue("@ID", PersonID);
                    OleDbDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        try
                        {
                            PersonLaybyTrans personLaybyTrans = new PersonLaybyTrans();
                            int i = 0;
                            personLaybyTrans.LaybyID = reader.GetInt32(i);
                            personLaybyTrans.SaleID = reader.GetInt32(++i);
                            personLaybyTrans.TimeDate = reader.GetDateTime(++i);
                            personLaybyTrans.Description = reader.IsDBNull(++i) ? null : reader.GetString(i);
                            personLaybyTrans.Retail = reader.GetInt32(++i);
                            personLaybyTrans.Cost = reader.GetInt32(++i);
                            personLaybyTrans.Quantity = reader.GetInt16(++i);
                            personLaybyTrans.GSTPaid = reader.GetInt32(++i);
                            SavePersonLaybyTrans(personLaybyTrans, LaybyTransDT, LaybyHeaderID);
                        }
                        catch (Exception ex)
                        {
                            Logger.LogWarningAndError("Error in Convert LaybyTrans ", ex);
                        }
                        if (LaybyTransDT.Rows.Count % BATCH_SAVE_SIZE == 0)
                        {
                            SaveLaybyTransDataTables(CpiamConnectionString, LaybyTransDT);
                        }
                    }
                    SaveLaybyTransDataTables(CpiamConnectionString, LaybyTransDT);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbConn.Close();
            }
        }

        private static void SaveLaybyTransDataTables(String ConnectionString, DataTable LaybyTransDT)
        {
            if (LaybyTransDT.Rows.Count > 0)
                dbSaveBatch.SaveBatch(LaybyTransDT, "LaybyTransFile", ConnectionString);

        }

        private static void SavePersonLaybyTrans(PersonLaybyTrans personLaybyTrans, DataTable LaybyTransDT, Guid LaybyHeaderID)
        {
            string strType = "A";
            Guid guidStkID = Guid.Empty;
            decimal decAmt = 0;
            decimal decSell = 0;
            string strItemCode=string.Empty;
            string GSTMarker = personLaybyTrans.GSTPaid >= 0 ? "*" : "";
            decimal GSTAmt = personLaybyTrans.GSTPaid / 100;
            if (personLaybyTrans.Description != null)
            {
                if (personLaybyTrans.Description.ToUpper() == "LAY-BY PAYMENT RECEIVED")
                {
                    strType = "P";
                    strItemCode = "LP";
                }
                else
                {
                    strType = "I";
                    strItemCode = personLaybyTrans.StockID.ToString();
                }
                if (strType == "P")
                {
                    decAmt = Math.Abs(personLaybyTrans.Retail / 100) * -1;
                    decSell = decAmt;
                }
                else
                {
                    decAmt = personLaybyTrans.Retail / 100;
                    decSell = decAmt * personLaybyTrans.Quantity;
                    if (personLaybyTrans.Quantity < 0)
                    {
                        decAmt = Math.Abs(decAmt) * -1;
                    }
                }

            }

            LaybyTransFile laybyTransFile = new LaybyTransFile(LaybyHeaderID, personLaybyTrans.TimeDate, strItemCode, personLaybyTrans.StockID, personLaybyTrans.Description.ToUpper(), personLaybyTrans.Quantity, strType, decAmt, GSTAmt, GSTMarker, decSell, personLaybyTrans.SaleID);
            LaybyTransFile.SaveLaybyTransToDataTable(laybyTransFile, LaybyTransDT);
        }

        private static Guid GetStockGUIDFromID(int StockID)
        {
            Guid StockGUID;
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"Select StkID from StockFile where StkNumber = @StockID");
            command.Parameters.AddWithValue("@StockID", StockID);
            SqlDataReader reader = command.ExecuteReader();
            reader.Read();
            StockGUID = reader.GetGuid(0);
            dbConn.Close();
            return StockGUID;
        }

        private static void UpdateCurBalAndTotBal()
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE A SET [DebtCurrBal] = B.[TotBalance], [DebtTotalBal] = B.[TotBalance]
            FROM [dbo].[DebtorHeaderFile] A INNER JOIN (SELECT [DebtHeaderID], SUM([DebtTransAmt]) AS [TotBalance] FROM [dbo].[DebtorTransFile]
            GROUP BY [DebtHeaderID]) B ON A.[DebtHeaderID] = B.[DebtHeaderID]");
            command.ExecuteNonQuery();
            dbConn.Close();
        }

        private static Guid CheckClientExists(string lastname, string firstname, string address, string postcode)
        {
            Guid ClientID = Guid.Empty;
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"SELECT ClientID
            FROM ClientsFile
            WHERE (ClientSurname = @Surname) AND (ClientFirstName = @FirstName) AND (ClientPostalAddress = @Address) AND (ClientPostalPCode = @PCode)");
            command.Parameters.AddWithValue("@Surname", lastname);
            command.Parameters.AddWithValue("@FirstName", firstname);
            command.Parameters.AddWithValue("@Address", address);
            command.Parameters.AddWithValue("@PCode", postcode);
            SqlDataReader reader = command.ExecuteReader();
            if (reader.Read())
            {
                ClientID = reader.IsDBNull(0) ? Guid.Empty : reader.GetGuid(0);
            }
            dbConn.Close();
            return ClientID;
        }

        private static Guid GetClientIDFromOLDPOSDebtorID(int AccountID)
        {
            Guid ClientID = Guid.Empty;
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"Select ClientID from DebtorHeaderFile where DebtExternDebtID=@AccountID");
            command.Parameters.AddWithValue("@AccountID", AccountID);
            SqlDataReader reader = command.ExecuteReader();
            if (reader.Read())
            {
                ClientID = reader.IsDBNull(0) ? Guid.Empty : reader.GetGuid(0);
            }
            dbConn.Close();
            return ClientID;
        }


        private static void UpdateLayby()
        {
            string ConnectionString = CpiamConnectionString;
            SqlConnection dbConn = new SqlConnection(ConnectionString);
            dbConn.Open();
            SqlCommand command = dbConn.CreateCommand();
            command.CommandText = string.Format(@"UPDATE A SET [LaybyTotal] = B.[TotBalance]
            FROM [dbo].[LaybyContracts] A INNER JOIN (SELECT [LaybyContractID], SUM([LaybySellPrice]) AS [TotBalance]
            FROM [dbo].[LaybyTransFile]
            WHERE [LaybyTransDescpt]<>'LAY-BY PAYMENT RECEIVED'
            GROUP BY [LaybyContractID]) B ON A.[LaybyContractID] = B.[LaybyContractID]");
            command.ExecuteNonQuery();
            command.CommandText = string.Format(@"UPDATE A SET [LaybyPaymentsTotal] = (B.[TotBalance]*-1)
            FROM [dbo].[LaybyContracts] A INNER JOIN (SELECT [LaybyContractID], SUM([LaybySellPrice]) AS [TotBalance]
            FROM [dbo].[LaybyTransFile] WHERE [LaybyTransDescpt]='LAY-BY PAYMENT RECEIVED' GROUP BY [LaybyContractID]) B ON A.[LaybyContractID] = B.[LaybyContractID]");
            command.ExecuteNonQuery();
            command.CommandText = string.Format(@"UPDATE LaybyContracts SET LaybyRemainingBal = LaybyTotal - LaybyPaymentsTotal");
            command.ExecuteNonQuery();
            command.CommandText = string.Format(@"UPDATE LaybyContracts SET LaybyStatus = 'F' WHERE (LaybyRemainingBal = 0)");
            command.ExecuteNonQuery();
            dbConn.Close();
        }

    }
}
